﻿
//----------------------------------------------------------------------------------------
// Copyright (C) 2008  银龙小子 版权所有。
//
// 文件编号：      
// 文 件 名：      CreateTableSql.cs
// 文件功能描述：   用来创建SQL语句
//
// 创建标识：      毛才君    2008-3-22 13:01:04
//
// 修改标识：
// 修改描述：
// 
// 修改标识：
// 修改描述：
//----------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Text;

namespace MaoCaiJun.DataBase.BuildProviders.BuildClass.MySql
{
    class CreateTableMySql
    {
        /// <summary>
        /// 创建SQL语句
        /// </summary>
        /// <param name="dbsAnalysisClass.Table">表名</param>
        /// <returns></returns>
        public static string CreateSql(MaoCaiJun.DataBase.Configuration.DbsAnalysis.Class dbsAnalysisClass)
        {
            if (dbsAnalysisClass.ID == null)
            {
                throw new Exception("在生成表 " + dbsAnalysisClass.Table + " 时 发现没有主键");
            }
            if (dbsAnalysisClass.ID.SqlIndex == null)
            {
                dbsAnalysisClass.ID.SqlIndex = "PK_" + dbsAnalysisClass.Table;
            }
            string[] strtypes = new string[] { "binary", "char", "nchar", "nvarchar", "varbinary", "varchar" };


            StringBuilder sql = new StringBuilder();

            #region CREATE TABLE
            sql.AppendLine("CREATE TABLE [" + dbsAnalysisClass.Table + "] (");
            sql.Append("[" + dbsAnalysisClass.ID.SqlName + "] " + GetSqlType(dbsAnalysisClass.ID.SqlType) + " ");
            //for (int ii = 0; ii < strtypes.Length; ii++)
            //{
            //    if (!string.IsNullOrEmpty(dbsAnalysisClass.ID.SqlLength) && strtypes[ii] == dbsAnalysisClass.ID.SqlType)
            //    {
            //        sql.Append(" (" + dbsAnalysisClass.ID.SqlLength + ") ");
            //    }
            //}
            sql.Append(" PRIMARY KEY");
            if (dbsAnalysisClass.ID.SqlNotNull)
            {
                sql.Append(" NOT NULL ");
            }
            else
            {
                sql.Append(" NULL ");
            }
            sql.AppendLine(",");
            //--

            for (int i = 0; i < dbsAnalysisClass.Property.Count; i++)
            {
                sql.Append("[" + dbsAnalysisClass.Property[i].SqlName + "] " + GetSqlType(dbsAnalysisClass.Property[i].SqlType) + " ");

                //for (int ii = 0; ii < strtypes.Length; ii++)
                //{
                //    if (!string.IsNullOrEmpty(dbsAnalysisClass.ID.SqlLength) && strtypes[ii] == dbsAnalysisClass.Property[i].SqlType)
                //    {
                //        sql.Append(" (" + dbsAnalysisClass.Property[i].SqlLength + ") ");
                //    }
                //}


                //
                if (dbsAnalysisClass.Property[i].SqlNotNull)
                {
                    sql.Append(" NOT NULL ");
                }
                else
                {
                    sql.Append(" NULL ");
                }
                //if (ModelDBClass[i].SqlValue!=string.Empty)
                //{
                //    sql.Append(" DEFAULT ('" + ModelDBClass[i].SqlValue + "') ");
                //}
                sql.AppendLine(",");

            }
            if (sql[sql.Length - 3] == ',') { sql[sql.Length - 3] = ')'; }
            //sql.AppendLine("GO");

            return sql.Replace("\"", "\"\"").ToString();
            #endregion CREATE TABLE
        }

        public static Dictionary<string, string> CreateSqlDictionary(MaoCaiJun.DataBase.Configuration.DbsAnalysis.Class dbsAnalysisClass)
        {


            if (dbsAnalysisClass.ID == null)
            {
                throw new Exception("在生成表 " + dbsAnalysisClass.Table + " 时 发现没有主键");
            }
            if (dbsAnalysisClass.ID.SqlIndex == null)
            {
                dbsAnalysisClass.ID.SqlIndex = "PK_" + dbsAnalysisClass.Table;
            }
            System.Collections.Generic.List<MaoCaiJun.DataBase.Configuration.DbsAnalysis.Property> _modelField = new List<MaoCaiJun.DataBase.Configuration.DbsAnalysis.Property>();
            _modelField.Add(dbsAnalysisClass.ID);
            _modelField.AddRange(dbsAnalysisClass.Property);


            System.Collections.Generic.Dictionary<string, string> sqlDictionary = new Dictionary<string, string>();

            sqlDictionary.Add(Config.DbTablePrefix + string.Format(Config.DbMemberMethodInsert, dbsAnalysisClass.Table),
"insert into [" + dbsAnalysisClass.Table + "](" + ParameterStr(_modelField, "[{0}]") + ") values (" + ParameterStr(_modelField, "@{0}") + ");");

            sqlDictionary.Add(Config.DbTablePrefix + string.Format(Config.DbMemberMethodUpdate, dbsAnalysisClass.Table),
"update [" + dbsAnalysisClass.Table + "] set " + ParameterStr(_modelField, "[{0}]=@{0}") + " where [" + dbsAnalysisClass.ID.SqlName + "]=@" + dbsAnalysisClass.ID.SqlName);

            sqlDictionary.Add(Config.DbTablePrefix + string.Format(Config.DbMemberMethodInsertUpdate, dbsAnalysisClass.Table), "");

            sqlDictionary.Add(Config.DbTablePrefix + string.Format(Config.DbMemberMethodDeleteByIndex, dbsAnalysisClass.Table),
"delete from [" + dbsAnalysisClass.Table + "] where [" + dbsAnalysisClass.ID.SqlName + "]=@" + dbsAnalysisClass.ID.SqlName);

            sqlDictionary.Add(Config.DbTablePrefix + string.Format(Config.DbMemberMethodDeleteDynamic, dbsAnalysisClass.Table),
"delete from [" + dbsAnalysisClass.Table + "] ");//TODO:err

            sqlDictionary.Add(Config.DbTablePrefix + string.Format(Config.DbMemberMethodSelect, dbsAnalysisClass.Table),
"select * from [" + dbsAnalysisClass.Table + "] where [" + dbsAnalysisClass.ID.SqlName + "]=@" + dbsAnalysisClass.ID.SqlName);

            sqlDictionary.Add(Config.DbTablePrefix + string.Format(Config.DbMemberMethodExists, dbsAnalysisClass.Table),
"select count(1) from [" + dbsAnalysisClass.Table + "] ");

            sqlDictionary.Add(Config.DbTablePrefix + string.Format(Config.DbMemberMethodGetMaxId, dbsAnalysisClass.Table),
"select count(1) from [" + dbsAnalysisClass.Table + "] ");

            return sqlDictionary;
        }

        private static string ParameterStr(System.Collections.Generic.List<MaoCaiJun.DataBase.Configuration.DbsAnalysis.Property> ModelDBField, string str)
        {
            string temp = "";
            for (int i = 0; i < ModelDBField.Count; i++)
            {
                temp += string.Format(str, ModelDBField[i].SqlName) + ",";
            }
            return temp.EndsWith(",") ? temp.Remove(temp.Length - 1) : temp;
        }

        public static string GetSqlType(string SqlType)
        {
            //访问类型名称      数据库数据类型      OLEDB 类型          .NET 框架类型           成员名称 
            //文本              VarWChar            DBTYPE_WSTR         System.String           OleDbType.VarWChar 
            //备注              LongVarWChar        DBTYPE_WSTR         System.String           OleDbType.LongVarWChar 
            //字节数            UnsignedTinyInt     DBTYPE_UI 1         System.Byte             OleDbType.UnsignedTinyInt 
            //是/否             Boolean             DBTYPE_BOOL         System.Boolean          OleDbType.Boolean 
            //日期/时间         DateTime            DBTYPE_DATE         System.DateTime         OleDbType.date 
            //货币              十进制              DBTYPE_NUMERIC      System.Decimal          OleDbType.numeric 
            //十进制数          十进制              DBTYPE_NUMERIC      System.Decimal          OleDbType.numeric 
            //双数              双                  DBTYPE_R8           System.Double           OleDbType.Double 
            //Autonumber （复制 ID） GUID           DBTYPE_GUID         System.Guid             OleDbType.guid 
            //复制 (ID) 号      GUID                DBTYPE_GUID         System.Guid             OleDbType.guid 
            //Autonumber （长整型） 整数            DBTYPE_I4           System.Int 32           OleDbType.integer 
            //数字: （长整型）  整数                DBTYPE_I4           System.Int 32           OleDbType.integer 
            //OLE 对象          LongVarBinary       DBTYPE_BYTES        数组 System.Byte        OleDbType.LongVarBinary 
            //单数              单个                DBTYPE_R4           System.Single           OleDbType.single 
            //整数:           SmallInt            DBTYPE_I2           System.Int 16           OleDbType.SmallInt 
            //二进制            VarBinary *         DBTYPE_BYTES        数组 System.Byte        OleDbType.binary 
            //超链接            VarWChar            DBTYPE_WSTR         System.String           OleDbType.VarWChar 

            switch (SqlType)
            {
                case "文本":
                    return "VarWChar";
                case "备注":
                    return "Memo";
                case "字节数":
                    return "UnsignedTinyInt";
                case "是/否":
                    return "yesno";
                case "日期/时间":
                    return "Date";
                case "货币":
                    return "money";
                case "十进制数":
                    return "Numeric";
                case "数字":
                    return "Integer";
                case "双数":
                    return "Double";
                case "单数":
                    return "Single";
                case "OLE 对象":
                    return "LongVarBinary";
                case "二进制":
                    return "Binary";
                case "整数":
                    return "Numeric";
                case "超链接":
                    return "SqlDbType.VarWChar";
                case "varchar":
                    return "VarChar";
                case "text":
                    return "Memo";
                case "image":
                    return "image";
                case "uniqueidentifier":
                    return "VarChar";
                case "tinyint":
                    return "TinyInt";
                case "smallint":
                    return "SmallInt";
                case "int":
                    return "Integer";
                case "smalldatetime":
                    return "Date";
                //case "real":
                //    return "SqlDbType.Real";
                case "money":
                    return "money";
                case "datetime":
                    return "Date";
                case "float":
                    return "Double";
                //case "sql_variant":
                //    return "SqlDbType.Variant";
                case "ntext":
                    return "LongVarChar";
                case "bit":
                    return "bit";
                case "decimal":
                    return "money";
                case "numeric":
                    return "Numeric";
                //case "smallmoney":
                //    return "SqlDbType.SmallMoney";
                case "bigint":
                    return "BigInt";
                case "varbinary":
                    return "Binary";
                case "binary":
                    return "Binary";
                case "char":
                    return "Char";
                case "timestamp":
                    return "DBTimeStamp";
                case "nvarchar":
                    return "nvarchar";
                case "nchar":
                    return "VarChar";
                //case "xml":
                //    return "SqlDbType.Xml";
                default:
                    return SqlType;
            }
        }

       
    }
}
